package com.dao.stock.index;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Properties;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.dao.exceptions.DataAccessException;
import com.dao.generalobject.GeneralObjectDao;
import com.dao.predictableobject.PredictableObject;
import com.dao.specyficobject.SpecyficObjectDAO;


public class StockIndexDaoImpl implements GeneralObjectDao,SpecyficObjectDAO {

	private final static Logger LOGGER = Logger.getLogger(StockIndexDaoImpl.class
			.getName());
	private String dbConfig = "config.txt";
	private Connection connection;
	PredictableObject predictableObject;
	public static String indexHistoricalDataStooq="www.stooq.pl";
	private String specificObjectName=StockIndex.TYPE;
	
	public StockIndexDaoImpl(PredictableObject predictableObject) throws IOException,DataAccessException{
		this.predictableObject=predictableObject;
		try {
			LOGGER.trace("Constructor: StockIndexDao("+predictableObject+")");
			loadProperties(dbConfig);
		} catch (FileNotFoundException e) {
			LOGGER.error("Couldont open property file: " + dbConfig, e);
			throw e;
		} catch (IOException e) {
			LOGGER.error("Couldn't load properties", e);
			throw e;
		} catch (SQLException e) {
			LOGGER.error("Couldn't establish connection to the database",e);
			throw new DataAccessException("Couldn't establish connection to the database",e);
		}		
	}
	private void loadProperties(String dbFileName)throws FileNotFoundException,
	IOException, SQLException {
		LOGGER.trace("method: loadProperties("+dbFileName+")");
		FileReader fr1 = new FileReader(dbFileName);
		BufferedReader br1 = new BufferedReader(fr1);
		Properties prop1 = new Properties();
		prop1.load(br1);
		connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@" + prop1.getProperty("host") + ":"
						+ prop1.getProperty("database"),
				prop1.getProperty("user"), prop1.getProperty("password"));
		
	}
	@Override
	public void batchCreateObjects(BufferedReader reader, String source)
			throws IOException, DataAccessException {
		// TODO Auto-generated method stub
		LOGGER.trace("There is no method of batch creating Indexes");

	}

	@Override
	public void deleteObject(int objId) throws DataAccessException {
		LOGGER.trace("method: deleteObject("+ objId+")");
		String sql="delete from stock_index where idobject="+predictableObject.getObjId()+
				" and idindex="+objId;
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}
	@Override
	public void deleteAllObjects() throws DataAccessException {
		LOGGER.trace("method: deleteAllObjects()");
		String sql="delete from stock_index where idobject="+predictableObject.getObjId();
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}
	@Override
	public StockIndex findObject(int objId) throws DataAccessException {
		LOGGER.trace("method: findObject("+objId+")");
		String sql="select * from stock_index where idobject="+predictableObject.getObjId()+" and idindex='"+objId+"'";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs=stmt.executeQuery();
			rs.next();
			StockIndex index = new StockIndex(predictableObject.getObjId(), predictableObject.getObjName(),predictableObject.getVariableMap(), objId, rs.getString("ticker"),
					rs.getString("name"),rs.getString("country"));
			stmt.close();
			return index;
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}

	@Override
	public StockIndex findObject(String name) throws DataAccessException {
		LOGGER.trace("method: findObject("+name+")");
		String sql="select * from stock_index where idobject="+predictableObject.getObjId()+" and ticker='"+name.toUpperCase()+"'";
		try {
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs=stmt.executeQuery();
			rs.next();
			StockIndex index = new StockIndex(predictableObject.getObjId(), predictableObject.getObjName(),predictableObject.getVariableMap(), rs.getInt("idindex"), rs.getString("ticker"),
					rs.getString("name"),rs.getString("country"));
			stmt.close();
			return index;
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
	}

	@Override
	public Vector<String> findAllNamesOfObjects()
			throws DataAccessException {
		LOGGER.trace("method:findAllNamesOfObjects()");
		String sql="select name from stock_index where idobject="+predictableObject.getObjId();
		Vector<String> indexes = new Vector<String>();
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()){
				indexes.add(rs.getString("name"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return indexes;
	}
	@Override
	public void batchSetVariablesData(BufferedReader reader, int objId,
			String method) throws DataAccessException, IOException {
		LOGGER.trace("method: batchSetVariablesData("+reader+","+objId+","+
			method+")");
		HashMap<String,PredictableObject.Variable> map = predictableObject.getVariableMap();
		if(method.contentEquals(indexHistoricalDataStooq)){
			String sqlHigh = "Insert into Index_Variables_Date(idIndex,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("HIGH").getVariableId()+",?,?)";
			String sqlLow = "Insert into Index_Variables_Date(idIndex,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("LOW").getVariableId()+",?,?)";
			String sqlClose = "Insert into Index_Variables_Date(idIndex,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("CLOSE").getVariableId()+",?,?)";
			String sqlOpen = "Insert into Index_Variables_Date(idIndex,idObject,idElement,var_date,value)" +
					"values ("+objId+","+predictableObject.getObjId()+","+map.get("OPEN").getVariableId()+",?,?)";
			String line;
			
			try {
				int high=-1;
				int low=-1;
				int close=-1;
				int open=-1;
				int date=-1;
				PreparedStatement stHigh = connection.prepareStatement(sqlHigh);
				PreparedStatement stLow = connection.prepareStatement(sqlLow);
				PreparedStatement stClose = connection.prepareStatement(sqlClose);
				PreparedStatement stOpen = connection.prepareStatement(sqlOpen);
				while ((line = reader.readLine()) != null) {
					int firstCharAsci =(int)line.substring(0, 1).getBytes()[0];
					if(line.startsWith("#")){
						LOGGER.debug("I read a commented line");
						continue;
					}
					else if (firstCharAsci>64 && firstCharAsci<91 || (firstCharAsci>96 && firstCharAsci<123)){
						LOGGER.debug("I read a line with variable names");
						String[] yahooStockInfo = line.split(",");
						for(int i=0;i<yahooStockInfo.length;i++){
							if(yahooStockInfo[i].toUpperCase().contentEquals("DATE"))
								date=i;
							else if(yahooStockInfo[i].toUpperCase().contentEquals("OPEN"))
								open=i;
							else if(yahooStockInfo[i].toUpperCase().contentEquals("CLOSE"))
								close=i;
							else if(yahooStockInfo[i].toUpperCase().contentEquals("HIGH"))
								high=i;
							else if(yahooStockInfo[i].toUpperCase().contentEquals("LOW"))
								low=i;
						}
					}
					else if(firstCharAsci>47 && firstCharAsci<58 ){
						LOGGER.debug("I read a line with variable values");
						LOGGER.debug("Date pos. :"+date+",High pos. :"+high+",Low pos. :"+low+",Close pos. :"+close+
								",Open pos. :"+open);
						if(date==-1 || high==-1 || low==-1 || close==-1 || open==-1){
							LOGGER.error("Didnt find variable name, see which has -1 value"+
									"Date pos. :"+date+",High pos. :"+high+",Low pos. :"+low+",Close pos. :"+close+
									",Open pos. :"+open);
							//TODO throw smth
						}
							
						else{
						String[] yahooStockInfo = line.split(",");
						String dateString=yahooStockInfo[date].substring(0, 4)+"-"
						+yahooStockInfo[date].substring(4, 6)+"-"+yahooStockInfo[date].substring(6, 8);
						LOGGER.debug("date: "+dateString);
						java.sql.Date dateDate=java.sql.Date.valueOf(dateString);
						stHigh.setDate(1,dateDate );
						stHigh.setFloat(2, Float.valueOf(yahooStockInfo[high]));
						stHigh.addBatch();
						stLow.setDate(1, dateDate);
						stLow.setFloat(2, Float.valueOf(yahooStockInfo[low]));
						stLow.addBatch();
						stOpen.setDate(1, dateDate);
						stOpen.setFloat(2, Float.valueOf(yahooStockInfo[open]));
						stOpen.addBatch();
						stClose.setDate(1, dateDate);
						stClose.setFloat(2, Float.valueOf(yahooStockInfo[close]));
						stClose.addBatch();
						}
					}

				}
				stHigh.executeBatch();
				stHigh.close();
				stLow.executeBatch();
				stLow.close();
				stOpen.executeBatch();
				stOpen.close();
				stClose.executeBatch();
				stClose.close();
				
			} catch (SQLException e) {
				LOGGER.error("Couldn't create or execute batch statement "+sqlHigh +" or " +sqlLow+" or "+
			sqlClose+" or "+ sqlOpen, e);
				throw new DataAccessException("Couldn't create or execute batch statement "+sqlHigh +" or " +sqlLow+" or "+
						sqlClose+" or "+ sqlOpen,e);
			} catch (IOException e) {
				LOGGER.error("Coludn't read source:" + reader, e);
				throw e;
			}
		}
		
		
	}
	@Override
	public int[] getObjectVariableLastDate(int objId, int varId)
			throws DataAccessException {
		LOGGER.trace("method: getObjectVariableLastDate("+objId+","+varId+")");
		String sql="select to_char(var_date,'YYYY') year, to_char(var_date,'ddd') day_of_year "+ 
   "from ( select var_date from index_variables_date"+
           " where idindex="+objId+
           " and idobject="+predictableObject.getObjId()+
           " and idelement="+varId+
           " order by var_date desc ) "+
   "where rownum <= 1";
		int[] date =new int[2];
		try {
			Statement stmt=connection.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next()==true){
				date[0]=Integer.valueOf(rs.getString("year"));
				date[1]=Integer.valueOf(rs.getString("day_of_year"));
			}
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		return date;
	}
	@Override
	public void clearVariablesData(int objId) throws DataAccessException {
		LOGGER.trace("method: clearVariablesData("+objId+")");
		String sql="delete from index_variables_date where idobject="+predictableObject.getObjId()+
				" and idindex="+objId;
		String sql2="delete from index_variables_date_pred where idobject="+predictableObject.getObjId()+
				" and idindex="+objId;
		try {
			Statement stmt=connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.executeUpdate(sql2);
			stmt.close();
		} catch (SQLException e) {
			LOGGER.error("Couldn't create or execute statement "+sql+" and "+sql2, e);
			throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
		}
		
	}
	@Override
	public String getSpecificObjectName() {
		LOGGER.trace("method: getSpecificObjectName()" );
		return specificObjectName;
	}
	
	public void createStockIndex(String ticker, String name,
			String country) throws DataAccessException {
	LOGGER.trace("createStockIndex("+ticker+","+name+","+country+")");
	String sql="Insert into stock_index(idIndex,idObject,ticker,name,country) " +
			"values(stock_index_seq.nextval,"+predictableObject.getObjId()+",'"+ticker.toUpperCase()+"','"
			+name.toUpperCase()+"','"+country.toUpperCase()+"')";
	try {
		Statement stmt = connection.createStatement();
		stmt.executeQuery(sql);
		stmt.close();
	} catch (SQLException e) {
		LOGGER.error("Couldn't create or execute statement "+sql, e);
		throw new DataAccessException("Coudlnt create or execute statement"+sql,e);
	}
		
	}
	@Override
	public void createHistoricalDataFile(int objId, String variableName,
			Calendar startDate, Calendar endDate) {
		LOGGER.trace("createHistoricalDataFile("+variableName+","+startDate+","+endDate+")");
		
		SimpleDateFormat format= new SimpleDateFormat("yyyy-mm-dd");
		String startDateString = format.format(startDate.getTime());
		String endDateString = format.format(endDate.getTime());
		
		
		
		String sql="select var_date ,value from index_variables_date where "+
		"idindex="+objId+" and idobject="+predictableObject.getObjId()+
		" and idelement="+predictableObject.getVariable(variableName).getVariableId()+
		" and var_date between to_date('"+startDateString+"','YYYY-MM-DD') and to_date('"+endDateString+"','YYYY-MM-DD')";
		String separator=",";
		String fileName="index_data.txt";
		try {
			CallableStatement cs=connection.prepareCall("{call dump_to_file(?,?,?)}");
			cs.setString(1, sql);
			cs.setString(2, separator);
			cs.setString(3, fileName);
			cs.execute();
		} catch (SQLException e) {
			LOGGER.error("Couldn't execute function dump to file("+sql+","+separator+","+fileName, e);
			throw new DataAccessException("Couldn't execute function dump to file("+sql+","+separator+","+fileName, e);
		}		
	}

}
